#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

DIM_MD_AREAS_SQL="
DROP FUNCTION IF EXISTS convert_geohash;
CREATE FUNCTION convert_geohash AS 'net.bwie.jtp.app.udf.ConvertGeoHash'
    USING JAR 'hdfs://node101:8020/warehouse/app_jars/zg6-jtp-log-1.0-SNAPSHOT.jar';

WITH
    temp_area AS (
        SELECT
            ID, AREANAME, PARENTID, BD09_LAT, BD09_LNG
        FROM jtp_app_ods.ods_md_areas
        WHERE dt='${data_date}'
    )
    , temp1 AS (
        SELECT
            ID, AREANAME AS province_name
        FROM temp_area
        WHERE PARENTID = 0
    )
    , temp2 AS (
        SELECT
            temp_area.ID
             , temp1.province_name
             , temp_area.AREANAME AS city_name
        FROM temp1
        LEFT JOIN temp_area ON temp1.ID = temp_area.PARENTID
    )
    , temp3 AS (
        SELECT
            temp_area.ID
             , temp2.province_name
             , temp2.city_name
             , temp_area.AREANAME AS district_name
        FROM temp2
        LEFT JOIN temp_area ON temp2.ID = temp_area.PARENTID
    )
INSERT OVERWRITE TABLE jtp_app_dim.dim_md_areas PARTITION (dt = '${data_date}')
SELECT
    temp_area.ID
    , temp3.province_name
    , temp3.city_name
    , temp3.district_name
    , temp_area.AREANAME AS street_name
    , temp_area.BD09_LAT
    , temp_area.BD09_LNG
    , convert_geohash(BD09_LAT, BD09_LNG, 6) AS geo_hash
FROM temp3
LEFT JOIN temp_area ON temp3.ID = temp_area.PARENTID
WHERE temp_area.bd09_lat IS NOT NULL  AND temp_area.bd09_lng IS NOT NULL
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"

